﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Transactions;
using System.Threading;

using System.Collections;

namespace InHouse.SharedLibraries.DataContext
{   
    public class DataContext : IDisposable
    {
        #region Private member variables
        /// <summary>
        /// shared connection for the database
        /// </summary>
        private SqlConnection conn = null;
        private Dictionary<string, string> _columnMapping;
        private CommandBehavior _readerCommandBehaviour;
        private int _commandTimeOut = 15;

        /// <summary>
        /// used to hold all the wait handles of the async operations in order to provide
        /// a simple wait method
        /// </summary>
        private List<WaitHandle> asyncWaitHandles;
        #endregion

        #region Construction
        /// <summary>
        /// Instantiates a new instance of the data context. Dispose should be called to tidy up the Sqlconnection.
        /// </summary>
        /// <param name="connectionStringName">The config name to use from the connectionString section of the config file</param>
        
        
        public DataContext(string connectionStringValue)
        {
            string ConString = "";
            ConString = connectionStringValue;
            
            conn = new SqlConnection(ConString);
            _columnMapping = new Dictionary<string, string>();
            _readerCommandBehaviour = CommandBehavior.CloseConnection;
            asyncWaitHandles = new List<WaitHandle>();
        }
        #endregion

        #region Public methods
       

       
        public DataTable FillDataTable(string commandText, ParamaterCollection Parms)
        {
            using (SqlCommand command = CreateNewCommand(commandText, Parms))
            {
                OpenConnection();

                DataSet dataCollection = null;

                try
                {
                    using (SqlDataReader reader = command.ExecuteReader(_readerCommandBehaviour))
                    {
                        //if (reader.HasRows)
                        //{
                            dataCollection = CreateDataSet(reader);
                        //}
                        ProceesParameters(command.Parameters, Parms);
                    }
                }
                catch (SqlException ex)
                {
                    Exception e = new DataContextException("A SQL error occured when filling the DataTable", ex);
                    
                    throw e;
                }

                if (dataCollection == null)
                    return null;
                else
                    return dataCollection.Tables[0];

                    
            }
        }

       

        public int Execute(string commandText, ParamaterCollection Parms)
        {
            using (SqlCommand command = CreateNewCommand(commandText, Parms))
            {
                OpenConnection();

                try
                {
                    int recordsAffected = command.ExecuteNonQuery();

                    ProceesParameters(command.Parameters, Parms);

                    

                    return recordsAffected;
                }
                catch (SqlException ex)
                {
                    Exception e = new DataContextException("A SQL error occured when trying to begin the execution of command", ex);
                    
                    throw e; 
                }
            }
        }

        private void ProceesParameters(SqlParameterCollection commandParms, ParamaterCollection parms)
        {
            if (parms != null)
            {

                parms.OutputParam = null;

                foreach (SqlParameter sqlParam in commandParms)
                {
                    if (parms.OutputParam == null)
                    {
                        parms.OutputParam = new Dictionary<string, object>();
                    }
                    parms.OutputParam.Add(sqlParam.ParameterName, sqlParam.Value);

                    
                }

            }
            
        }

        

       

        

        #endregion

        #region IDisposable Members

        public void Dispose()
        {

            conn.Close();
            //conn.Dispose();
        }

        #endregion

        #region Public properties
        /// <summary>
        /// A dictionary representing a mapping between class properties and DB columns
        /// By default the class property name is mapped 1 to 1 with the column name unless a mapping is specified in this collection
        /// </summary>
        public Dictionary<string, string> PropertyMapping
        {
            get
            {
                return _columnMapping;
            }
            set
            {
                if (value == null)
                    throw new ArgumentNullException("PropertyMapping cannot be set to null");

                _columnMapping = value;
            }
        }

        public CommandBehavior ReaderCommandBehaviour
        {
            get
            {
                return _readerCommandBehaviour;
            }
            set
            {
                _readerCommandBehaviour = value;
            }
        }

        /// <summary>
        /// The timeout of the SqlCommand in seconds.
        /// The default value is 45 seconds
        /// </summary
        public int CommandTimeout
        {
            get
            {
                return _commandTimeOut;
            }
            set
            {
                _commandTimeOut = value;
            }
        }
        #endregion

        #region Private methods
        private void BuildDataContextData(IDictionary exceptionData)
        {
            
            exceptionData.Add("Server", conn.DataSource);
            exceptionData.Add("Database", conn.Database);
            exceptionData.Add("ConnectionTimeoutSeconds", conn.ConnectionTimeout);
            
        }

        
        /// <summary>
        /// Opens the connection to the DBContext's database
        /// </summary>
        private void OpenConnection()
        {
            try
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
            }
            catch (SqlException ex)
            {
                Exception e = new DataContextException("An error occured opening the connection to the database", ex);
                BuildDataContextData(e.Data);
                throw e;
            }
            catch (InvalidOperationException ex)
            {
                Exception e = new DataContextException("The connection to database was already open", ex);
                BuildDataContextData(e.Data);
                throw e;
            }
        }





        private SqlCommand CreateNewCommand(string commandText, ParamaterCollection param)
        {
            SqlCommand command = null;
            ParamaterCollection.ParamInstance instance;

            if (commandText.IndexOf(" ") == -1) // must be sp
            {
                command = new SqlCommand(commandText);
                command.CommandType = CommandType.StoredProcedure;

                
            }
            else // Parameterised SQL
            {
                command = new SqlCommand(commandText);

                if (param != null)
                {
                    if (param.HasItems())
                    {
                        int intCurrent_Param = 0;
                        while (intCurrent_Param < param.Count())
                        {
                            if (param.GetParam(intCurrent_Param).Type == SqlDbType.Structured)
                                throw new DataContextException("Structured data types are unsupported for Parameterised SQL commands.");
                            command.Parameters.Add(param.GetParam(intCurrent_Param).Name, param.GetParam(intCurrent_Param).Type);
                            command.Parameters[intCurrent_Param].Value = param.GetParam(intCurrent_Param).Value;
                            intCurrent_Param++;
                        }
                    }
                }
            }

            command.Connection = conn;
            return command;
        }





        private DataSet CreateDataSet(SqlDataReader reader)
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(CreateDataTable(reader));

            while (reader.NextResult())
            {
                ds.Tables.Add(CreateDataTable(reader));
            }

            return ds;

            

        }
        

        private DataTable CreateDataTable(SqlDataReader reader)
        {
            DataTable dt = new DataTable();

            for (int columnCount = 0; columnCount < reader.FieldCount; columnCount++)
			{
                dt.Columns.Add(reader.GetName(columnCount), reader.GetFieldType(columnCount));
			}

            while (reader.Read())
            {
                DataRow row = dt.NewRow();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    row[i] = reader[i];
                }
                dt.Rows.Add(row);
            }

            return dt;
        }
        #endregion
    }

    public class ParamaterCollection
    {
        public bool HasItems()
        {
            if (this != null)
            {
                if (strParam_Collection != null)
                {
                    if (strParam_Collection.Length != 0)
                    {
                        return true;
                    }
                }

                return false;
            }
            else
            {
                return false;
            }

        }

        public int Count()
        {
            if (strParam_Collection != null)
            {
                return strParam_Collection.Length;
            }

            return 0;
        }

        public struct ParamInstance
        {
            public string Name;
            public System.Data.SqlDbType Type;
            public string Value;
            public ParameterDirection Direction;
            public object ListValue;
        }

        public ParamInstance GetParam(int Index)
        {
            ParamInstance paramInstance = new ParamInstance();
            paramInstance.Name = strParam_Collection[Index];
            paramInstance.Type = Param_Collection_Types[Index];
            paramInstance.Value = strParam_Collection_Values[Index];
            paramInstance.Direction = strParam_Collection_Direction[Index];
            paramInstance.ListValue = listParam_Collection_Values[Index];

            return paramInstance;
        }

        public ParamaterCollection()
        {

        }

        public ParamaterCollection(object ParamObject)
        {
            FieldInfo[] fields = ParamObject.GetType().GetFields(BindingFlags.Public | BindingFlags.Instance);
            PropertyInfo[] properties = ParamObject.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);

            foreach (FieldInfo field in fields)
            {
                object Val = field.GetValue(ParamObject);
                if (Val != null)
                {
                    Add(field.Name, Val);
                }
            }

            foreach (PropertyInfo prop in properties)
            {
                object Val = prop.GetValue(ParamObject,null);
                if (Val != null)
                {
                    Add(prop.Name, Val);
                }
            }
        }  

        public ParamaterCollection(string[] Array)
        {            
                int Current = 0;

                while (Current < Array.Length)
                {
                    Add(Array[Current], Array[Current + 1]);
                    Current = Current + 2;
                }
            
        }

        public object this[string key]
        {
            get
            {
                return OutputParam[key];
            }
            set
            {
                OutputParam[key] = value;
            }
        }

        public Dictionary<string,object> OutputParam;
        private string[] strParam_Collection;
        private SqlDbType[] Param_Collection_Types;
        private string[] strParam_Collection_Values;
        private ParameterDirection[] strParam_Collection_Direction;
        private object[] listParam_Collection_Values;

        public void Add<T>(string ParameterName, T value)
        {
            Add<T>(ParameterName, SqlDbType.VarChar, value);
        }

        public void Add(string ParameterName, int value)
        {
            Add(ParameterName, SqlDbType.Int, value.ToString());
        }

        public void Add(string ParameterName, string value)
        {
            Add(ParameterName, SqlDbType.VarChar, value);
        }

        public void Add(string ParameterName, DateTime value)
        {
            Add(ParameterName, SqlDbType.DateTime, value.ToString("yyyy-MM-ddTHH:mm:ss"));
        }

        public void Add<T>(string ParameterName, T value, ParameterDirection Direction)
        {
            Add<T>(ParameterName, SqlDbType.VarChar, value);
            strParam_Collection_Direction[strParam_Collection_Direction.Length - 1] = Direction;
        }

        public void Add(string ParameterName, int value, ParameterDirection Direction)
        {
            Add(ParameterName, SqlDbType.Int, value.ToString());
            strParam_Collection_Direction[strParam_Collection_Direction.Length - 1] = Direction;
        }

        public void Add(string ParameterName, string value, ParameterDirection Direction)
        {
            Add(ParameterName, SqlDbType.VarChar, value);
            strParam_Collection_Direction[strParam_Collection_Direction.Length - 1] = Direction;
        }

        public void Add(string ParameterName, DateTime value, ParameterDirection Direction)
        {
            Add(ParameterName, SqlDbType.DateTime, value.ToString("yyyy-MM-dd HH:mm:ss"));
            strParam_Collection_Direction[strParam_Collection_Direction.Length - 1] = Direction;
        }

        public void Add<T>(string ParameterName, SqlDbType objDbType, T value, ParameterDirection Direction)
        {
            Add(ParameterName, objDbType, value);
            strParam_Collection_Direction[strParam_Collection_Direction.Length - 1] = Direction;
        }

        public void Add<T>(string ParameterName, SqlDbType objDbType, T value)
        {
            //int intBase_Length = 0;
            if (strParam_Collection != null)
            {
                //strParam
                string[] strTemp = new string[strParam_Collection.Length + 1];
                SqlDbType[] strTemp_Types = new SqlDbType[Param_Collection_Types.Length + 1];
                string[] strTemp_Values = new string[strParam_Collection.Length + 1];
                ParameterDirection[] strTemp_Direction = new ParameterDirection[strParam_Collection.Length + 1];
                object[] dtTemp_Values = new object[listParam_Collection_Values.Length + 1];
                
                int intCurrent = 0;
                foreach (string str1 in strParam_Collection)
                {
                    strTemp[intCurrent] = str1;
                    strTemp_Types[intCurrent] = Param_Collection_Types[intCurrent];
                    strTemp_Values[intCurrent] = strParam_Collection_Values[intCurrent];
                    strTemp_Direction[intCurrent] = strParam_Collection_Direction[intCurrent];
                    dtTemp_Values[intCurrent] = listParam_Collection_Values[intCurrent];
                    intCurrent++;
                }

                strTemp[intCurrent] = ParameterName;
                strTemp_Types[intCurrent] = objDbType;

                if (value == null)
                {
                    value = default(T);
                }

                if (value != null)
                {
                    if (objDbType == SqlDbType.Structured)
                        dtTemp_Values[intCurrent] = (object)value;
                    else
                        strTemp_Values[intCurrent] = value.ToString();
                }

                strTemp_Direction[intCurrent] = ParameterDirection.Input;

                strParam_Collection = strTemp;
                Param_Collection_Types = strTemp_Types;
                strParam_Collection_Values = strTemp_Values;
                strParam_Collection_Direction = strTemp_Direction;
                listParam_Collection_Values = dtTemp_Values;
            }
            else // must be null
            {
                string[] strTemp = new string[1];
                SqlDbType[] strTemp_Types = new SqlDbType[1];
                string[] strTemp_Values = new string[1];
                ParameterDirection[] strTemp_Direction = new ParameterDirection[1];
                object[] dtTemp_Values = new object[1];

                strTemp[0] = ParameterName;
                strTemp_Types[0] = objDbType;
                strTemp_Direction[0] = ParameterDirection.Input;
                if (objDbType == SqlDbType.Structured)
                    dtTemp_Values[0] = value;
                else
                    strTemp_Values[0] = value.ToString();

                strParam_Collection = strTemp;
                Param_Collection_Types = strTemp_Types;
                strParam_Collection_Values = strTemp_Values;
                strParam_Collection_Direction = strTemp_Direction;
                listParam_Collection_Values = dtTemp_Values;
            }
        }
    }

    public class DataContextTableType : Attribute
    {
        protected string _TableTypeName;

        public DataContextTableType(string tableTypeName)
        {
            _TableTypeName = tableTypeName;
        }

        public string TableTypeName
        {
            get { return _TableTypeName; }
            set { _TableTypeName = value; }
        }
    }

    public class DataContextTableColumn : Attribute
    {
        protected int _ColIndex;
        protected string _ColName; // Optional.
        protected SqlDbType _ColType;
        protected long _VarLength; // Optional.

        public DataContextTableColumn(int columnIndex, SqlDbType type)
        {
            _ColIndex = columnIndex;
            _ColType = type;
            _ColName = null;
            _VarLength = 1000;
        }

        public DataContextTableColumn(int columnIndex, SqlDbType type, string columnName)
        {
            _ColIndex = columnIndex;
            _ColType = type;
            _ColName = columnName;
            _VarLength = 1000;
        }

        public DataContextTableColumn(int columnIndex, SqlDbType type, long length)
        {
            _ColIndex = columnIndex;
            _ColType = type;
            _ColName = null;
            _VarLength = length;
        }

        public DataContextTableColumn(int columnIndex, SqlDbType type, long length, string columnName)
        {
            _ColIndex = columnIndex;
            _ColType = type;
            _ColName = columnName;
            _VarLength = length;
        }

        public int ColumnIndex
        {
            get { return _ColIndex; }
            set { _ColIndex = value; }
        }

        public string ColumnName
        {
            get
            {
                if (_ColName == null)
                    return "Column_" + _ColIndex.ToString();
                return _ColName; 
            }
            set { _ColName = value; }
        }

        public SqlDbType ColumnType
        {
            get { return _ColType; }
            set { _ColType = value; }
        }

        public long ColumnSize
        {
            get { return _VarLength; }
            set { _VarLength = value; }
        }
    }

    class TableStructure
    {
        public int columnIndex;
        public string memberName;
        public string columnName;
        public Type memberType;
        public SqlDbType columnType;
        public int columnSize;
    }
}
